﻿CREATE PROCEDURE [dbo].[USPTOOLST_DBFindStringInDBObjects_Extended]
	@parameter [varchar](255) = null,
	@database [varchar](255) = 'All',
	@Lines [int] = 20,
	@ShowFullProc [bit] = 0
WITH EXECUTE AS CALLER
AS
/*
-- next section used for editing in query analyzer
DECLARE	@parameter	varchar(255),
	@database	varchar(255),
	@Lines		int,
	@ShowFullProc	bit
Set @parameter = 'titleauthor + royalty'
Set @database = 'pubs'
Set @Lines = 20
Set @ShowFullProc = 0

Sample:
exec USPTOOLST_DBFindStringInDBObjects_Extended 'firstdate', 'nTIMS'
*/

IF ISNULL(@parameter,'') = '' 			
	or charindex( char(10), @parameter ) > 0	
	or charindex( char(13), @parameter ) > 0	
BEGIN

	print	'exec spr_grep  [[@parameter=]''search string''], ' + CHAR(13) +
		'               [[@database=]''database name''], '+ CHAR(13) + 
		'               OPTIONAL [[@lines=]''number of lines around the search string to display''], ' + CHAR(13) +
		'               OPTIONAL [[@ShowFullProc=]''indicator to display all the lines in a stored procedure''], ' + CHAR(13) +
		CHAR(13) + 
		'Analysis of @paramter modified from the first stored procedure: ' + CHAR(13) +
		'   Execute spr_grep "{string1}operation1{string2}operation2{string3}...", [case]' + CHAR(13) +
		'   - stringN is a string of characters up to 80 characters long, ' + CHAR(13) +
		'       enclosed in curly brackets. Brackets may be omitted if stringN ' + CHAR(13) +
		'       does not contain leading and trailing spaces or characters: +,-,&.' + CHAR(13) +
		'   - operationN is one of the characters: +,-,&. Interpreted as or,minus,and.' + CHAR(13) +
		'       Operations are executed from left to right with no priorities.' + CHAR(13) +
		'   E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"' + CHAR(13) +
		'        will search for all objects that have an occurence of string "alpha"' + CHAR(13) +
		'        or string "beta gamma", do not have string "delta", ' + CHAR(13) +
		'        and have string "+++".'  + CHAR(13) + 
		CHAR(13) +
		'Note that parameter string may not contain <CarriageReturn> or <LineFeed> characters.' + CHAR(13) +
		CHAR(13) +
		'Analysis of @database: ' + CHAR(13) +
		'  search either database name, ''All'', or ''ActualAll''. ' + CHAR(13) +
		'  default behavior to search ''All'' ' + CHAR(13) +
		'  ''All'' will not search databases on excluded list (@ExcludedList)' + CHAR(13) +
		'  ''ActualAll'' will search all databases, including those on the excluded list ' +CHAR(13) +
		CHAR(13) +
		'example: exec Northwind.dbo.spr_grep ''titleauthor + royalty'', ''pubs'''
    RETURN
END

declare @str_no         	tinyint, 		-- from the first script	
        @msg_str_no     	varchar(3),
        @operation      	char(1), 
        @string         	varchar(80), 
        @oper_pos       	smallint,
        @context        	varchar(255),
        @i              	tinyint,
        @longest        	tinyint,
        @msg            	varchar(255)

declare	@ProcID			int,			-- from the second script
	@ProcName		sysname,
	@lineNbr		int,
	@ProcLine		nvarchar(1000),
	@ProcChunk		nvarchar(4000),
	@ProcChunkLen		int,
	@SubStringStart		int,
	@SubStringEnd		int,
	@CRIdx			int,
	@LFIdx			int,
	@DelimLen		smallint,
	@PartialLine		nvarchar(1000),
	@ProcLineMatch		int

declare	@Query			varchar(7000),		-- additional variables
	@ExcludedDatabases	varchar(2000),
	@CurrentParameter	varchar(255)


Set @ExcludedDatabases = '''master'',''model'',''msdb'',''tempdb'''

select  @parameter = ltrim(rtrim(@parameter))

create table #search ( str_no tinyint, operation char(1), string varchar(80), last_obj int )
create table #found_objects ( id int, str_no tinyint )
create table #result ( id int )
CREATE TABLE #Results (DatabaseName varchar(250), ObjectName varchar(250), Owner varchar(250), ObjectType varchar(250), ObjectID int)
/* Parse the parameter string */
select @str_no = 0
while datalength( @parameter ) > 0
begin
	/* Get operation */
	select @str_no = @str_no + 1, @msg_str_no = rtrim( convert( char(3), @str_no + 1 ) )
	if @str_no = 1
		select  @operation = '+'
	else 
	begin
		if substring( @parameter, 1, 1 ) in ( '+', '-', '&' )
			select  @operation = substring( @parameter, 1, 1 ),
				@parameter = ltrim( right( @parameter, datalength( @parameter ) - 1 ) )
		else
		begin
			select @context = rtrim( substring( 
	                	@parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
			select @msg = 'Incorrect or missing operation sign before "' + @context + '".'
			print  @msg 
			select @msg = 'Search string ' + @msg_str_no + '.'
			print  @msg 
			return
		end
	end
	
	/* Get string */
	if datalength( @parameter ) = 0
	begin
		print 'Missing search string at the end of the parameter.'
		select @msg = 'Search string ' + @msg_str_no + '.'
		print  @msg 
		return
	end
	if substring( @parameter, 1, 1 ) = '{'
	begin
		if charindex( '}', @parameter ) = 0
		begin
			select @context = rtrim( substring( 
	        	      @parameter + space( 255 - datalength( @parameter) ), 1, 200 ) )
			select @msg = 'Bracket not closed after "' + @context + '".'
			print  @msg 
			select @msg = 'Search string ' + @msg_str_no + '.'
			print  @msg 
			return
		end
		if charindex( '}', @parameter ) > 82
		begin
			select @context = rtrim( substring( 
				@parameter + space( 255 - datalength( @parameter) ), 2, 20 ) )
			select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
			print  @msg 
			select @msg = 'String begins with "' + @context + '".'
			print  @msg 
			return
		end        
		select  @string    = substring( @parameter, 2, charindex( '}', @parameter ) - 2 ),
		      @parameter = ltrim( right( @parameter, 
                      datalength( @parameter ) - charindex( '}', @parameter ) ) )
	end
	else
	begin
		/* Find the first operation sign */
		select @oper_pos = datalength( @parameter ) + 1
		if charindex( '+', @parameter ) between 1 and @oper_pos
		  select @oper_pos = charindex( '+', @parameter )
		if charindex( '-', @parameter ) between 1 and @oper_pos
		  select @oper_pos = charindex( '-', @parameter )
		if charindex( '&', @parameter ) between 1 and @oper_pos
		  select @oper_pos = charindex( '&', @parameter )
		if @oper_pos = 1
		begin
			select @context = rtrim( substring( 
				@parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
			select @msg = 'Search string ' + @msg_str_no + 
			        ' is missing, before "' + @context + '".'
			print  @msg 
			return
		end        
		if @oper_pos > 81
		begin
			select @context = rtrim( substring( 
			      @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
			select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
			print  @msg 
			select @msg = 'String begins with "' + @context + '".'
			print  @msg 
			return
		end        
		select  @string    = ltrim(rtrim(substring( @parameter, 1, @oper_pos - 1 ))),
			@parameter = ltrim( right( @parameter, 
			datalength( @parameter ) - @oper_pos + 1 ) )
	end
	insert #search values ( @str_no, @operation, @string, 0 )
end
select @longest = max( datalength( string ) ) - 1
from   #search


Set @Query = 	'DECLARE DatabaseCursor CURSOR FOR 
			SELECT name
			FROM master.dbo.sysdatabases '
IF @database <> 'ActualAll'
BEGIN	
	IF @database <> 'All'
		Set @Query = @Query + 'WHERE name = ''' + @database + ''''
	ELSE 
	BEGIN
		Set @Query = @Query + 'WHERE NOT(name IN(' + @ExcludedDatabases + ')) '
	END
END
exec(@Query)


OPEN DatabaseCursor
FETCH NEXT FROM  DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN

	/* ------------------------------------------------------------------ */
	Set @Query =	'insert #found_objects
			select a.id, c.str_no
			from    ' + @database + '.dbo.syscomments a, #search c
			where  charindex( c.string, a.text ) > 0

			insert #found_objects
			select a.id, c.str_no
			from    ' + @database + '.dbo.syscomments a,  ' + @database + '.dbo.syscomments b, #search c
			where  a.id = b.id
				and a.number = b.number
				and a.colid + 1 = b.colid
				and charindex( c.string, 
			        	right( a.text, ' + CONVERT(varchar(50),@longest) + ') + 
					space( 255 - datalength( a.text ) ) +
			        	substring( b.text, 1, ' + CONVERT(varchar(50),@longest) + ' ) ) > 0'
	exec(@Query)

	/* ------------------------------------------------------------------ */
	select distinct str_no, id into #dist_objects from #found_objects
	create unique clustered index obj on #dist_objects  ( str_no, id )
	select @i = 0
	while @i < @str_no
	begin
	    select @i = @i + 1
	    select @operation = operation from #search where str_no = @i
	    
	    if @operation = '+'
	        insert #result
	        select id
	        from   #dist_objects 
	        where  str_no = @i
	    else if @operation = '-'
	        delete #result
	        from   #result a, #dist_objects b
	        where  b.str_no = @i
	        and    a.id = b.id
	    else if @operation = '&'
	        delete #result
	        where  not exists 
	                ( select 1
	                  from   #dist_objects b
	                  where  b.str_no = @i
	                  and    b.id = #result.id )
	end
	/* Select results */
	select distinct id into #dist_result from #result

	Set @Query = 	'SELECT DISTINCT ''' + @database + ''' AS DatabaseName,
				sysobjects.name AS Name,
				Owner = user_name(uid),
				Object_type = substring(spt_values.name, 1, 16),
				#dist_result.id
			FROM #dist_result,
				' + @database + '.dbo.sysobjects AS sysobjects, 
				master.dbo.spt_values  AS spt_values
			WHERE #dist_result.id = sysobjects.id
				and sysobjects.sysstat = spt_values.number
				and spt_values.type = ''O''
			ORDER BY Object_type, Name'

	INSERT INTO #Results
		exec(@Query)

	DROP TABLE #dist_objects
	DROP TABLE #dist_result

	FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


SELECT DatabaseName, ObjectName, ObjectType
FROM #Results
ORDER BY DatabaseName, ObjectName
DECLARE DetailDatabaseCursor CURSOR FOR 
	SELECT DISTINCT DatabaseName
	FROM #Results 
	ORDER BY DatabaseName
OPEN DetailDatabaseCursor
FETCH NEXT FROM  DetailDatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN

	declare @ProcLines table(DatabaseName varchar(500),
			 	LineNbr  int,
			 	ProcLine nvarchar(3000))

	Set @Query = 'declare ProcIds cursor for
			SELECT DISTINCT #results.ObjectId, sysobjects.Name
			FROM #Results
			  INNER JOIN ' + @database + '.dbo.sysobjects AS sysobjects
			    ON #results.ObjectID = sysobjects.ID
			ORDER BY sysobjects.Name'
	Exec(@Query)
	open ProcIds
	fetch next from ProcIds into @ProcId, @ProcName
	
	while @@FETCH_STATUS = 0
	begin

		set @LineNbr = 1
		set @ProcLine = ''
	 
		Set @Query = 'declare ProcDef cursor for 
				SELECT RTRIM(text)
				FROM ' + @database + '.dbo.syscomments
				WHERE id = (SELECT id
						FROM ' + @database + '.dbo.sysobjects
						WHERE name = ''' + @ProcName + ''')
				ORDER BY colid'
		Exec(@Query)
		open ProcDef
		fetch next from ProcDef into @ProcChunk
		while @@FETCH_STATUS = 0
		     begin
			set @ProcChunkLen = len(@ProcChunk)
			set @SubStringStart = 1
			set @SubStringEnd = 0

			while @SubStringStart <= @ProcChunkLen
			     begin
				set @CRIdx = charindex(char(13), @ProcChunk, @SubStringStart)
				set @LFIdx = charindex(char(10), @ProcChunk, @SubStringStart)
				   
				if @CRIdx = 0 and @LFIdx = 0
				     begin
					set @SubStringEnd = @ProcChunkLen + 1
					set @DelimLen = 0
				     end
				else
				     begin
					if @CRIdx < @LFIdx
					     begin
						if @CRIdx = 0
						     begin
							set @SubStringEnd = @LFIdx
							set @DelimLen = 1
						     end
						else
						     begin
							set @SubStringEnd = @CRIdx 
	      
							 if @LFIdx - @CRIdx = 1
							 	set @DelimLen = 2
							 else
								set @DelimLen = 1
						     end
					     end
					else
					     begin
						if @LFIdx = 0
						     begin
							set @SubStringEnd = @ProcChunkLen + 1
							set @DelimLen = 0
						     end
						else
						     begin
							set @SubStringEnd = @LFIdx
							set @DelimLen = 1
						     end
					     end
				     end
	
				if @SubStringStart > @ProcChunkLen set @SubStringStart = @ProcChunkLen
	
				set @ProcLine = rtrim(substring(@ProcChunk, @SubStringStart, @SubStringEnd - @SubStringStart))
				set @SubStringStart = @SubStringEnd + @DelimLen
				set @ProcLine = @PartialLine + @ProcLine
				set @PartialLine = ''
	
				if @DelimLen = 0 and len(@ProcLine) > 0
				     begin
					set @PartialLine = @ProcLine
				     end
				else
				     begin
					INSERT INTO @ProcLines
						(
							LineNbr
							, ProcLine
						)
						VALUES
						(
							@LineNbr
							, @ProcLine
						)
	    
					set @LineNbr = @LineNbr + 1
					set @ProcLine = ''
				     end
			     end
 
			fetch next from ProcDef into @ProcChunk
		    end

		close ProcDef
		deallocate ProcDef


		CREATE TABLE #LineNumbers (LineNumbers int)
		DECLARE LineNumbers CURSOR FOR
			SELECT string
			FROM #search
			WHERE operation <> '-'
		OPEN LineNumbers
		FETCH NEXT FROM LineNumbers INTO @CurrentParameter
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO #LineNumbers
				SELECT LineNbr
				FROM @ProcLines
				WHERE CHARINDEX(@CurrentParameter,ProcLine,0) > 0

			FETCH NEXT FROM LineNumbers INTO @CurrentParameter
		END
		CLOSE LineNumbers
		DEALLOCATE LineNumbers


		declare ProcLineMatches cursor local for
			SELECT DISTINCT LineNumbers
			FROM #LineNumbers
			Order BY LineNumbers

		open ProcLineMatches
		
		fetch next from ProcLineMatches into @ProcLineMatch
		
		set rowcount @Lines
		
		while @@FETCH_STATUS = 0
		     begin
			print @ProcName + ' (at line ' + cast(@ProcLineMatch as varchar) + ')' + char(13) + 
				char(10) + '================================================'
			
			SELECT '0' AS LineNbr, 
				'======== ' + @ProcName + ' (at line ' + cast(@ProcLineMatch as varchar) + ') ========' AS ProcLine
			FROM @ProcLines
			WHERE 1 = 1
			UNION
			SELECT LineNbr, ProcLine
			FROM @ProcLines
			WHERE LineNbr > @ProcLineMatch - (@Lines / 2)
			ORDER BY LineNbr
			
			print ''
			
			fetch next from ProcLineMatches into @ProcLineMatch
		     end
		
		close ProcLineMatches
		deallocate ProcLineMatches
		DROP TABLE #LineNumbers

		set rowcount 0
	

		if @ShowFullProc = 1
		     begin	
			SELECT LineNbr
					, ProcLine
				FROM @ProcLines
				ORDER BY LineNbr
		     end
		
		delete @ProcLines
		
		fetch next from ProcIds into @ProcId, @ProcName
	     end
	
	close ProcIds
	deallocate ProcIds


	FETCH NEXT FROM  DetailDatabaseCursor INTO @database
END
CLOSE DetailDatabaseCursor
DEALLOCATE DetailDatabaseCursor



DROP TABLE #search
DROP TABLE #found_objects
DROP TABLE #result
DROP TABLE #Results


